﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SQLite;
using System.IO;
using System.Reflection;

using NUnit.Framework;

namespace Pvax.Data.Tests
{
    [SetUpFixture]
    public class TestSuite: TestBase
    {
        [SetUp]
        public void Init()
        {
            // SQLite set up
            if(File.Exists(Globals.SQLiteDbName))
                File.Delete(Globals.SQLiteDbName);
            // MDB set up
            using(Stream resource = Assembly.GetExecutingAssembly().GetManifestResourceStream(typeof(TestSuite), "Test.mdb"))
            using(Stream output = File.Create(Globals.JetDbName))
            {
                resource.CopyTo(output);
            }
            using(SQLiteConnection connection = new SQLiteConnection(Globals.SQLiteConnectionString))
            {
                InitDb(connection);
            }
            using(OleDbConnection connection = new OleDbConnection(Globals.OleDbConnectionString))
            {
                InitDb(connection);
            }
        }

        static void ExecuteSqlForTable(IDbConnection connection, string sqlFormatText, string tableName)
        {
            ExecuteSql(connection, String.Format(sqlFormatText, tableName));
        }

        static void InitDbForTables(IDbConnection connection, string tableName1, string tableName2)
        {
            ExecuteSqlForTable(connection, "CREATE TABLE {0} (Id INTEGER PRIMARY KEY,  FirstName TEXT NULL, MiddleName TEXT NULL, LastName TEXT NULL)", tableName1);
            ExecuteSqlForTable(connection, "CREATE TABLE {0} (Id INTEGER PRIMARY KEY, PersonId INTEGER, CountryCode INTEGER, AreaCode INTEGER, [Number] TEXT NULL)", tableName2);
            // Fill in the tables
            // NB: the tests depend on the contents of the tables!
            ExecuteSqlForTable(connection, "INSERT INTO {0} (Id, FirstName, MiddleName, LastName) VALUES (1, 'Harry', '', 'Potter')", tableName1);
            ExecuteSqlForTable(connection, "INSERT INTO {0} (Id, FirstName, MiddleName, LastName) VALUES (2, 'Ender', '', 'Wiggin')", tableName1);
            ExecuteSqlForTable(connection, "INSERT INTO {0} (Id, FirstName, MiddleName, LastName) VALUES (3, 'Gulliver', '', 'Foyle')", tableName1);
            ExecuteSqlForTable(connection, "INSERT INTO {0} (Id, FirstName, MiddleName, LastName) VALUES (4, 'Leif', '', 'Barker')", tableName1);
            ExecuteSqlForTable(connection, "INSERT INTO {0} (Id, PersonId, CountryCode, AreaCode, [Number]) VALUES (1, 1, 7, 888, '822-01-21')", tableName2);
            ExecuteSqlForTable(connection, "INSERT INTO {0} (Id, PersonId, CountryCode, AreaCode, [Number]) VALUES (2, 1, 7, 811, '555-01-21')", tableName2);
            ExecuteSqlForTable(connection, "INSERT INTO {0} (Id, PersonId, CountryCode, AreaCode, [Number]) VALUES (3, 2, 7, 811, '991-01-21')", tableName2);
        }

        static void InitDb(IDbConnection connection)
        {
            connection.Open();
            InitDbForTables(connection, "Persons", "Phones");
            InitDbForTables(connection, "OptimizedPersons", "OptimizedPhones");
        }

        [TearDown]
        public void Done()
        {
            File.Delete(Globals.SQLiteDbName);
            File.Delete(Globals.JetDbName);
        }
    }
}
